package com.lbd.system.service.impl;

import com.lbd.project.bean.TargetInfo;
import com.lbd.system.controller.vo.HomeVo;
import com.lbd.system.service.HomeService;
import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * 描述：
 * 创建时间2016/12/26 13:40
 * 创建人 yangzhuo
 */
@Service
public class HomeServiceImpl implements HomeService {
    private final Logger log = Logger.getLogger(this.getClass());
    @Autowired
    public EntityManager em;

    @Override
    public HomeVo find(Date todayStart) {
        HomeVo home = new HomeVo();
        Map<String, Object> parameter = new HashMap<String, Object>();
        HashMap<String, Object> map = new HashMap<String, Object>();
        StringBuffer sql = new StringBuffer();
        parameter.put("now", todayStart);
        //查询今日新增用户数
        try {
            sql.append("select COUNT(*) as today_new_user from user where 1=1");
            sql.append(" and created_date >= :now");
            map = (HashMap<String, Object>) findMapByPageNativeQuery(parameter, sql.toString());
            home.setTodayNewUser(map == null || map.get("today_new_user") == null ? 0 : Integer.valueOf(map.get("today_new_user").toString()));
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询今日新增用户数失败,e=" + e.getMessage());
        }

        //查询今日登陆用户数
        try {
            sql = new StringBuffer();
            sql.append("select COUNT(*) as today_login_user from user where 1=1");
            sql.append(" and last_login_date >= :now");
            map = (HashMap<String, Object>) findMapByPageNativeQuery(parameter, sql.toString());
            home.setTodayLoginUser(map == null || map.get("today_login_user") == null ? 0 : Integer.valueOf(map.get("today_login_user").toString()));
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询今日新增用户数失败,e=" + e.getMessage());
        }
        //查询今日用户总数
        try {
            sql = new StringBuffer();
            sql.append("select COUNT(*) as sum from user where 1=1");
            map = (HashMap<String, Object>) findMapByPageNativeQuery(null, sql.toString());
            home.setHistoryUser(map == null || map.get("sum") == null ? 0 : Integer.valueOf(map.get("sum").toString()));
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询今日新增用户数失败,e=" + e.getMessage());
        }

        return home;
    }

    public Map findLoanInfo() {
        Map<String, Object> map = new HashMap<>();
        StringBuffer sql = new StringBuffer();
        Map<String, Object> result = new HashMap<>();
        try {
            sql.append("select COUNT(*) as wclLoanNum from loan where app_status = 0");
            result = (HashMap<String, Object>) findMapByPageNativeQuery(null, sql.toString());
            map.put("wclLoanNum", result.get("wclLoanNum"));
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询未处理的借款申请失败,e=" + e.getMessage());
        }

        try {
            sql = new StringBuffer();
            sql.append("select COUNT(*) as reviewTargetNum from target_info where audit_status = 1");
            result = (HashMap<String, Object>) findMapByPageNativeQuery(null, sql.toString());
            map.put("reviewTargetNum", result.get("reviewTargetNum"));
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询未处理的借款申请失败,e=" + e.getMessage());
        }
        return map;
    }

    /**
     * 获取平台运营总数据
     *
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Map findInvestment(Date startDate, Date endDate) {
        Map<String, Object> map = new HashMap<>();

        Map<String, Object> parameter = new HashMap<String, Object>();
        StringBuffer sqlStr = new StringBuffer();
        if (startDate != null && startDate.toString().length() > 0) {
            sqlStr.append(" and created_date >= :startDate");
            parameter.put("startDate", startDate);

        }
        if (endDate != null && endDate.toString().length() > 0) {
            sqlStr.append(" and created_date <= :endDate");
            parameter.put("endDate", endDate);
        }
        StringBuffer sql = new StringBuffer();
        Map<String, Object> result = new HashMap<>();
        //查询总用户数
        try {
            sql.append("select COUNT(*) as user from user where 1=1");
            sql.append(sqlStr);
            result = (HashMap<String, Object>) findMapByPageNativeQuery(parameter, sql.toString());
            map.put("user", result.get("user"));
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询用户数失败,e=" + e.getMessage());
        }

        //投资金额
        try {
           /* sql = new StringBuffer();
            sql.append("SELECT SUM(buy_amt)AS invest,SUM(IFNULL(real_income,0)) AS money FROM `order` where 1=1");
            sql.append(sqlStr);
            result = (HashMap<String, Object>) findMapByPageNativeQuery(parameter,sql.toString());*/
            //map.put("invest",result.get("invest"));
            map.put("invest", 0.0);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询投资金额失败,e=" + e.getMessage());
        }

        //回款金额
        try {
            parameter = new HashMap<String, Object>();
            sqlStr = new StringBuffer();
            if (startDate != null && startDate.toString().length() > 0) {
                sqlStr.append(" and hkrq >= :startDate");
                parameter.put("startDate", startDate);

            }
            if (endDate != null && endDate.toString().length() > 0) {
                sqlStr.append(" and hkrq <= :endDate");
                parameter.put("endDate", endDate);
            }

            sql = new StringBuffer();
            sql.append("SELECT SUM(bj)AS totalBj,SUM(IFNULL(lx,0)) AS totalLx FROM payback_plan where 1=1");
            sql.append(sqlStr);
            result = (HashMap<String, Object>) findMapByPageNativeQuery(parameter, sql.toString());
            BigDecimal totalBj = BigDecimal.ZERO;
            if (map != null && map.get("totalBj") != null) {
                totalBj = BigDecimal.valueOf((double) map.get("totalBj"));
            }
            BigDecimal totalLx = BigDecimal.ZERO;
            if (map != null && map.get("totalLx") != null) {
                totalLx = BigDecimal.valueOf((double) map.get("totalLx"));
            }
            map.put("return", totalBj.add(totalLx));
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询回款金额失败,e=" + e.getMessage());
        }

        //总借款标数量
        try {
            parameter = new HashMap<String, Object>();
            sqlStr = new StringBuffer();
            if (startDate != null && startDate.toString().length() > 0) {
                sqlStr.append(" and audit_time >= :startDate");
                parameter.put("startDate", startDate);

            }
            if (endDate != null && endDate.toString().length() > 0) {
                sqlStr.append(" and audit_time <= :endDate");
                parameter.put("endDate", endDate);
            }

            //即将开放、已满标、还款中、已完结
            sql = new StringBuffer();
            sql.append("SELECT count(*) as total FROM target_info where 1=1");
            sql.append(" and target_state in ('" + TargetInfo.TARGET_STATE_INVEST + "','" + TargetInfo.TARGET_STATE_FULL + "','" +
                    TargetInfo.TARGET_STATE_PAYBACK + "','" + TargetInfo.TARGET_STATE_ENDING + "')");
            sql.append(sqlStr);

            result = (HashMap<String, Object>) findMapByPageNativeQuery(parameter, sql.toString());

            map.put("targetNum", result.get("total"));
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询借款标数量失败,e=" + e.getMessage());
        }

        return map;
    }


    /**
     * nativesql返回Map
     *
     * @param parameter
     * @param nativeSql
     * @return
     */
    public Map findMapByPageNativeQuery(Map<String, Object> parameter, String nativeSql) {
        Query nativeQuery = em.createNativeQuery(nativeSql);
        nativeQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        if (parameter != null) {
            for (String key : parameter.keySet()) {
                nativeQuery.setParameter(key, parameter.get(key));
            }
        }
        return (Map) nativeQuery.getSingleResult();
    }


}
